﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DDF.Data.Providers;
using System.Data.SqlClient;
using System.Data;
using ARR.Entities.Gateways;

namespace ARR.DataModel.Repositories
{
    public static partial class TempArticleSalesRepository
    {
        public static void TruncateTable()
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;                
                database.NewCommand("TRUNCATE TABLE TempArticleSales ", System.Data.CommandType.Text);

                database.ExecuteNonQuery();
            }
        }

        public static void UpdateArticleCodes(string[] articleCodes)
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.NewCommand("TempArticleSales_UpdateArticleCodes", CommandType.StoredProcedure);
                database.AddInParameter("@ArticleCodes", DbType.String, String.Join(";", articleCodes));
                database.ExecuteNonQuery();
            }
        }

        public static void UpdateArticleIds(int rangeReviewId)
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;
                database.NewCommand("TempArticleSales_UpdateArticleIds", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.ExecuteNonQuery();
            }
        }

        public static void UpdateStoreIds()
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;
                database.NewCommand("TempArticleSales_UpdateStoreIds", CommandType.StoredProcedure);
                database.ExecuteNonQuery();
            }
        }

        public static void DeleteZeroSales()
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;
                database.NewCommand("Delete From TempArticleSales Where isnull(Units,0) <= 0 and isnull(sales,0) <=0", CommandType.Text);
                database.ExecuteNonQuery();
            }
        }
        

        public static List<TempArticleSaleRow> SelectByIdList(int[] ids)
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.NewCommand("TempArticleSales_SelectByIdList", CommandType.StoredProcedure);
                database.AddInParameter("@ids", DbType.String, String.Join(",", ids));
                
                IDataReader reader = database.ExecuteReader();

                List<TempArticleSaleRow> rows = new List<TempArticleSaleRow>();

                Load(reader, rows);

                return rows;
            }
        }

        public static IDataReader SelectCategoryMargins()
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.NewCommand("TempArticleSales_SelectCategoryMargins", CommandType.StoredProcedure);
                return database.ExecuteReader();
            }
        }

        public static void BulkCopy(DataTable salesToCopy)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
                {
                    //LG: Agregar ColumnMappings para todos los Bulk Copies
                    bulk.DestinationTableName = "TempArticleSales";

                    //Ventas
                    bulk.ColumnMappings.Add("BonusBuyNumber", "BonusBuyNumber");
                    bulk.ColumnMappings.Add("StoreId", "StoreId");
                    bulk.ColumnMappings.Add("ArticleId", "ArticleId");
                    bulk.ColumnMappings.Add("Date", "Date");
                    bulk.ColumnMappings.Add("Units", "Units");
                    bulk.ColumnMappings.Add("Sales", "Sales");
                    bulk.ColumnMappings.Add("GM", "GM");
                    bulk.ColumnMappings.Add("GrossSales", "GrossSales");
                    bulk.ColumnMappings.Add("Price", "Price");
                    bulk.ColumnMappings.Add("NetMargin", "NetMargin");
                    bulk.ColumnMappings.Add("Costs", "Costs");
                    bulk.ColumnMappings.Add("CostsWithVat", "CostsWithVat");


                    //Articulos
                    bulk.ColumnMappings.Add("RangeReviewId", "RangeReviewId");
                    bulk.ColumnMappings.Add("CategoryId", "CategoryId");
                    bulk.ColumnMappings.Add("NotFoundCategory", "NotFoundCategory");
                    bulk.ColumnMappings.Add("ArticleCode", "ArticleCode");
                    bulk.ColumnMappings.Add("ArticleName", "ArticleName");
                    bulk.ColumnMappings.Add("EAN", "EAN");
                    bulk.ColumnMappings.Add("UOM", "UOM");
                    bulk.ColumnMappings.Add("Brand", "Brand");
                    bulk.ColumnMappings.Add("Manufacturer", "Manufacturer");
                    bulk.ColumnMappings.Add("TaxRate", "TaxRate");

                    //Tiendas
                    bulk.ColumnMappings.Add("CountryId", "CountryId");
                    bulk.ColumnMappings.Add("StoreCode", "StoreCode");
                    bulk.ColumnMappings.Add("StoreName", "StoreName");
                    bulk.ColumnMappings.Add("Region", "Region");

                    bulk.BulkCopyTimeout = 900;
                    bulk.WriteToServer(salesToCopy);
                    bulk.Close();
                }

                conn.Close();
            }
        }

        public static List<TempArticleSaleRow> SelectDuplicates()
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.NewCommand("TempArticleSales_SelectDuplicates", CommandType.StoredProcedure);

                IDataReader reader = database.ExecuteReader();

                List<TempArticleSaleRow> rows = new List<TempArticleSaleRow>();

                Load(reader, rows);

                return rows;
            }
        }

    }
}
